package com.example.qrcodecamera;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class MaterialCSVDbHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "NewCSVData.db";
    private static final int DATABASE_VERSION = 2;
    private static final String TABLE_NAME = "csv_records";

    public MaterialCSVDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TABLE_NAME + " (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "material_id TEXT," +        // 物料编号
                "material_name TEXT," +      // 物料名称
                "gross_weight REAL," +       // 总重量
                "net_weight REAL," +         // 当前重量
                "timestamp TEXT)");          // 入库时间
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    // 批量插入方法
    // MaterialCSVDbHelper.java（关键代码）
    public boolean bulkInsert(List<MaterialCSVRecord> records) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            db.beginTransaction();
            for (MaterialCSVRecord record : records) {
                ContentValues values = new ContentValues();
                values.put("material_id", record.getMaterialId());
                values.put("material_name", record.getMaterialName());
                values.put("gross_weight", record.getTotalWeight());
                values.put("net_weight", record.getCurrentWeight());
                values.put("timestamp", record.getStorageTime());
                db.insert(TABLE_NAME, null, values);
            }
            db.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            Log.e("DBInsert", "插入失败", e);
            return false;
        } finally {
            db.endTransaction();
        }
    }

    // 分页查询
    public List<MaterialCSVRecord> getPagedData(int page, int pageSize) {
        List<MaterialCSVRecord> records = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(
                "SELECT * FROM " + TABLE_NAME +
                        " LIMIT " + pageSize +
                        " OFFSET " + (page-1)*pageSize, null);

        if (cursor.moveToFirst()) {
            do {
                MaterialCSVRecord record = new MaterialCSVRecord(
                        cursor.getInt(0),
                        cursor.getString(1),  // material_id
                        cursor.getString(2),  // material_name
                        cursor.getDouble(3), // gross_weight (总重量)
                        cursor.getDouble(4), // net_weight (当前重量)
                        cursor.getString(5)  // timestamp
                );
                records.add(record);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return records;
    }

    // 获取总记录数
    public int getTotalCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        return count;
    }

    public boolean deleteRecord(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            int rowsAffected = db.delete(TABLE_NAME, "id = ?", new String[]{String.valueOf(id)});
            return rowsAffected > 0;
        } catch (Exception e) {
            Log.e("DBDelete", "删除失败", e);
            return false;
        } finally {
            db.close();
        }
    }

    public List<MaterialCSVRecord> getMaterialById(String materialId) {
        List<MaterialCSVRecord> data = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_NAME,
                new String[]{"id", "material_id", "material_name", "gross_weight", "net_weight", "timestamp"},
                "material_id = ?",
                new String[]{materialId},
                null, null, null
        );

        if (cursor.moveToFirst()) {
            do {
                MaterialCSVRecord info = new MaterialCSVRecord(
                        cursor.getInt(0),          // id
                        cursor.getString(1),       // material_id
                        cursor.getString(2),       // material_name
                        cursor.getDouble(3),       // gross_weight
                        cursor.getDouble(4),       // net_weight
                        cursor.getString(5)        // timestamp
                );
                data.add(info);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return data;
    }
}